#include "databasewindow.h"
#include "ui_databasewindow.h"
#include <QDebug>
DataBaseWindow::DataBaseWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::DataBaseWindow)
{
    ui->setupUi(this);
    initTable();
}

DataBaseWindow::~DataBaseWindow()
{
    delete ui;
}

void DataBaseWindow::initTable()
{
    QSqlQuery query("select *from info");
    QSqlRecord rec = query.record();

    int cols = rec.count();

    ui->tableWidget->setColumnCount(cols);
    ui->tableWidget->setColumnCount(cols + 1);
    QStringList headList;
    for(int i = 0;i < cols; ++i)
    {
        headList.append(rec.fieldName(i));
    }
    headList.append("操作");

    ui->tableWidget->setHorizontalHeaderLabels(headList);

    while(query.next())
    {
        QString id = query.value(0).toString();
        QString name = query.value(1).toString();
        QString age = query.value(2).toString();

        int rows = ui->tableWidget->rowCount();
        ui->tableWidget->insertRow(rows);

        ui->tableWidget->setItem(rows,0,new QTableWidgetItem(id));
        ui->tableWidget->setItem(rows,1,new QTableWidgetItem(name));
        ui->tableWidget->setItem(rows,2,new QTableWidgetItem(age));

        // 在最后一列（新增的操作列）添加按钮
        QPushButton *deleteButton = new QPushButton("删除");
        ui->tableWidget->setCellWidget(rows, cols, deleteButton);

        // 连接按钮的点击信号到删除行的槽函数，并传递当前行索引
        connect(deleteButton, &QPushButton::clicked, this, [=](){
            ui->tableWidget->setFocus();
            int r = ui->tableWidget->currentRow();
            deleteRow(r);
        });
    }
}

// 删除行函数
void DataBaseWindow::deleteRow(int row)
{
    // 以id列作为唯一标识来删除对应行数据
    QSqlQuery query;
    QString id = ui->tableWidget->item(row, 0)->text();  // 获取要删除行的id值
    query.prepare("DELETE FROM info WHERE id =?");
    query.bindValue(0, id);
    if (!query.exec())
    {
        ui->statusbar->showMessage(query.lastError().text());
    }

    // 从表格中删除该行
    ui->tableWidget->removeRow(row);
}

void DataBaseWindow::on_addAction_triggered()
{
 #if 0
    DeviceInfoDialog  *dlg = new DeviceInfoDialog;
    dlg->setAttribute(Qt::WA_DeleteOnClose);
    dlg->show();

    connect(dlg,&DeviceInfoDialog::sentDeviceInfoDialog,[=](QString name,QString age){
        QSqlQuery query;
        QString sql = QString("INSERT INTO info (\"name\",\"age\") VALUES ('%2',%3)").arg(name).arg(age.toInt());

        if(!query.exec(sql))
        {
            ui->statusbar->showMessage(query.lastError().text());
        }
    });
#endif

    //实例化QSqlQuery对象
    QSqlQuery query;

    QString name =  QInputDialog::getText(this,"name","请输入姓名");
    int age = QInputDialog::getInt(this,"age","请输入年龄",0,0,100);
    //准备Sql语句
    query.prepare("INSERT INTO info (name,age)" "VALUES (?,?)");
    query.bindValue(0,name);
    query.bindValue(1,age);

    //执行Sql语句
    if(!query.exec())
    {
        ui->statusbar->showMessage(query.lastError().text());
    }

}

void DataBaseWindow::on_delAction_triggered()
{
    ui->tableWidget->clearContents();
    ui->tableWidget->setRowCount(0);

    QSqlQuery query;
    query.prepare("DELETE FROM info WHERE name = ? ");
    query.bindValue(0,ui->tableWidget->item(ui->tableWidget->currentRow(),0)->text());

    if(!query.exec())
    {
        ui->statusbar->showMessage(query.lastError().text());
        return;
    }

    while(query.next())
    {
        QString id = query.value(0).toString();
        QString name = query.value(1).toString();
        QString age = query.value(2).toString();

        int rows = ui->tableWidget->rowCount();
        ui->tableWidget->insertRow(rows);

        ui->tableWidget->setItem(rows,0,new QTableWidgetItem(id));
        ui->tableWidget->setItem(rows,1,new QTableWidgetItem(name));
        //ui->tableWidget->setItem(rows,2,new QTableWidgetItem(age));
        ageBox = new QSpinBox;
        ageBox->setRange(0,100);
        ageBox->setValue(age.toInt());
        ui->tableWidget->setCellWidget(rows,2,ageBox);

    }
}

void DataBaseWindow::on_modityAction_triggered()
{
    ui->tableWidget->clearContents();
    ui->tableWidget->setRowCount(0);

    QSqlQuery query;
    while(query.next())
    {
        QString id = query.value(0).toString();
        QString name = query.value(1).toString();
        QString age = query.value(2).toString();

        int rows = ui->tableWidget->rowCount();
        ui->tableWidget->insertRow(rows);

        ui->tableWidget->setItem(rows,0,new QTableWidgetItem(id));
        ui->tableWidget->setItem(rows,1,new QTableWidgetItem(name));
        ui->tableWidget->setItem(rows,2,new QTableWidgetItem(age));

    }
}

void DataBaseWindow::on_selectAction_triggered()
{
    ui->tableWidget->clearContents();
    ui->tableWidget->setRowCount(0);

    QSqlQuery query;
    query.prepare("SELECT *FROM info WHERE age > ? and age < ?;");
    query.bindValue(0,ui->formSpinBox->value());
    query.bindValue(1,ui->toSpinBox->value());
    query.exec();

    while(query.next())
    {
        QString id = query.value(0).toString();
        QString name = query.value(1).toString();
        QString age = query.value(2).toString();

        int rows = ui->tableWidget->rowCount();
        ui->tableWidget->insertRow(rows);

        ui->tableWidget->setItem(rows,0,new QTableWidgetItem(id));
        ui->tableWidget->setItem(rows,1,new QTableWidgetItem(name));
        ui->tableWidget->setItem(rows,2,new QTableWidgetItem(age));

    }
}

void DataBaseWindow::on_exportAction_triggered()
{
    //遍历表格数据,将表格数据保存到.csv文件中
    QString fileName = QFileDialog::getSaveFileName(this,"save data",".","excel(*.csv)");
    if (fileName.isEmpty())
    {
        return;  // 如果用户取消选择文件，则直接返回
    }
    QFile file(fileName);

    bool isOpen = file.open(QIODevice::WriteOnly);
    if(!isOpen)
    {
        ui->statusbar->showMessage("无法打开文件进行写入");
        return;
    }
    int cols = ui->tableWidget->columnCount();
    int rows = ui->tableWidget->rowCount();
    QString lines;

    for(int i = 0; i < rows; i++)
    {
        for(int j = 0; j < cols - 1; j++)
        {
            lines += ui->tableWidget->item(i,j)->text()+ ",";
        }
        //去掉最后一个","分隔符
        lines.chop(1);
        lines += "\r\n";
        file.write(lines.toUtf8());
        lines.clear();
    }
    file.close();
    ui->statusbar->showMessage("数据已成功导出");
}
